#!/usr/bin/env python
# coding=utf-8
import sys, httplib,string
import os, sys, re, traceback, logging
import urllib
import time
import datetime
import getopt
import math
import urllib2
from pyExcelerator import *

cookie="rtime=0; ltime=1290664259985; cnzz_eid=45688920-1290661000-; JSESSIONID=19E8242CD4E8802878B653422414B7C1"
def getPage(get_url):
    try:
        #conn = httplib.HTTPConnection('nianhui.edu927.com')
        conn = httplib.HTTPConnection('byts.cn')
        #headers = {"Host":"nianhui.edu927.com","User-Agent":"Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.9) Gecko/20100315 Firefox/3.5.9","Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8","Accept-Language":"en-us,en;q=0.5","Accept-Encoding":"gzip,deflate","Accept-Charset":"ISO-8859-1,utf-8;q=0.7,*;q=0.7","Keep-Alive":"300","Connection":"keep-alive","Referer":"http://nianhui.edu927.com/url.php?id=1","Content-Type":"application/x-www-form-urlencoded","Content-Length":"14"}
        headers = {"Host":"byts.cn",
                "User-Agent":"Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.10) Gecko/20100914 Firefox/3.6.10",
                "Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
                "Accept-Language":"en-us,en;q=0.5",
                "Accept-Encoding":"gzip,deflate",
                "Accept-Charset":"ISO-8859-1,utf-8;q=0.7,*;q=0.7",
                "Keep-Alive":"115",
                "Connection":"keep-alive",
                #"Referer":"http://byts.cn/work/login.jsp",
                "Cookie":cookie,
                #"Content-Type":"application/x-www-form-urlencoded",
                #"Content-Length":"73"
         }
        
        conn.request('GET', get_url, "",headers)
        result = conn.getresponse()
        status = result.status
        res='ERR'
        if status==200:
            res = result.read().decode("utf-8",'ignore').encode('utf-8')
        conn.close()
    except Exception,e:
        print e
    return res
        

pat1 = Pattern()
pat1.pattern = Pattern.SOLID_PATTERN
pat1.pattern_fore_colour = 49

pat2 = Pattern()
pat2.pattern = Pattern.SOLID_PATTERN
pat2.pattern_fore_colour = 1

fnt_title = Font()
fnt_title.name = 'Arial'
fnt_title.colour_index = 0
fnt_title.bold = True
fnt_article = Font()
fnt_article.name = 'Arial'
fnt_article.colour_index = 0
fnt_article.bold = False

fnt_price1 = Font()
fnt_price1.name = 'Arial'
fnt_price1.colour_index = 2
fnt_price1.bold = True
fnt_price2 = Font()
fnt_price2.name = 'Arial'
fnt_price2.colour_index = 4
fnt_price2.bold = True

borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1

al_left = Alignment()
al_left.horz = Alignment.HORZ_LEFT
al_left.vert = Alignment.VERT_CENTER

al_center = Alignment()
al_center.horz = Alignment.HORZ_CENTER
al_center.vert = Alignment.VERT_CENTER

style_title1 = XFStyle()
style_title1.font = fnt_title
style_title1.borders = borders
style_title1.alignment = al_left
style_title1.pattern = pat1

style_title2 = XFStyle()
style_title2.font = fnt_title
style_title2.borders = borders
style_title2.alignment = al_left
style_title2.pattern = pat2

style_title3 = XFStyle()
style_title3.font = fnt_article
style_title3.borders = borders
style_title3.alignment = al_left
style_title3.pattern = pat2


style_title4 = XFStyle()
style_title4.font = fnt_article
style_title4.borders = borders
style_title4.alignment = al_center
style_title4.pattern = pat2

style_title5 = XFStyle()
style_title5.font = fnt_price1
style_title5.borders = borders
style_title5.alignment = al_center

style_title6 = XFStyle()
style_title6.font = fnt_price2
style_title6.borders = borders
style_title6.alignment = al_center

pageNum=0

url='http://byts.cn/work/product_list.jsp?status=2&page=%s'
re_products=re.compile(r'<a\s*href="product_show\.jsp\?id=([0-9]+)">\s*([^<]*)</a>', re.S|re.X)
re_nextPage=re.compile(r"<font\s*size='\d*'\s*color='red'>(\d*)</font>\s*<a\s*href='([^']*)'", re.S|re.X)
re_schedule=re.compile(r"从</td>[^>]*>([^<]*)</td>[^>]*>到</td>[^>]*>([^<]*)</td>[^>]*>交通:</td>[^>]*>([^<]*)</td>[^>]*>住宿:</td>[^>]*>([^<]*)</td>[^>]*>用餐:</td>[^>]*>([^<]*)</td>[^>]*>[^>]*>[^>]*>[^>]*>[^>]*>[^>]*>[^>]*>([\S\s]*?</td>)", re.X|re.S)
re_prices=re.compile(r"checkbox[^>]*>[^>]*>[^>]*>[^>]*>[^>]*>\s*([0-9]{4}-[0-9]{2}-[0-9]{2})[\S\s]*?结算价[^>]*>\s*([0-9]*)\s*</font>[\S\s]*?结算价[^>]*>\s*([0-9]*)\s*</font>[\S\s]*?结算价[^>]*>\s*([0-9]*)\s*</font>[\S\s]*?外卖价[^>]*>\s*([0-9]*)\s*</font>[\S\s]*?外卖价[^>]*>\s*([0-9]*)\s*</font>[\S\s]*?外卖价[^>]*>\s*([0-9]*)\s*</font>", re.X|re.S)

def writeXls(get_url):
    
    global pageNum
    pageNum=pageNum+1
    try:
        html=getPage(get_url)
        prds=re_products.findall(html)
        for p in prds:
            try:
                w = Workbook()
                fname=p[0]+'_'+re.compile(r"[^\\/\:\*\?\"<>\|\[\]]*", re.S|re.X).search(p[1]).group()
                ws=w.add_sheet(fname.decode('utf-8'))
                print p[1]
                try:
                    html2=getPage('http://byts.cn/work/product_show.jsp?id='+p[0])
                except Exception,e2:
                    print 'http://byts.cn/work/product_show.jsp?id='+p[0]
                    print e2
                ws.write_merge(0, 0, 0, 9, u'线路基本信息', style_title1)
                ws.write(1, 0, u'线路编号', style_title2)
                strid=re.compile(r"ID\s*号:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if strid:
                    strid=strid.replace('</td>','')
                ws.write_merge(1, 1, 1, 3,strid.decode('utf-8'), style_title3)
                
                ws.write(1, 4, u'供应商', style_title2)
                vender=re.compile(r"供应商:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if vender:
                    vender=vender.replace('</td>','')
                ws.write_merge(1, 1, 5, 9, vender.decode('utf-8'), style_title3)
                
                ws.write(2, 0, u'联系人', style_title2)
                linkman=re.compile(r"联系人:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if linkman:
                    linkman=linkman.replace('</td>','')
                ws.write_merge(2, 2, 1, 3, linkman.decode('utf-8'), style_title3)
                ws.write(2, 4, u'联系电话', style_title2)
                phone=re.compile(r"联系电话:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if phone:
                    phone=phone.replace('</td>','')
                ws.write_merge(2, 2, 5, 6, phone.decode('utf-8'), style_title3)
                ws.write(2, 7, u'传真号码', style_title2)
                faxnum=re.compile(r"传真号码:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if faxnum:
                    faxnum=faxnum.replace('</td>','')
                ws.write_merge(2, 2, 8, 9, faxnum.decode('utf-8'), style_title3)
                
                ws.write(3, 0, u'线路类型', style_title2)
                ws.write_merge(3, 3, 1, 3, u'请选择线路类型(自由行、跟团游、本地参团)', style_title3)
                ws.write(3, 4, u'主题', style_title2)
                subject=re.compile(r"主\s*题:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if subject:
                    subject=subject.replace('</td>','')
                ws.write_merge(3, 3, 5, 9, subject.decode('utf-8'), style_title3)
                
                ws.write(4, 0, u'出发城市', style_title2)
                ws.write_merge(4, 4, 1, 3, u'', style_title3)
                ws.write(4, 4, u'目的地', style_title2)
                dest=re.compile(r"目地省份:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if dest:
                    dest=dest.replace('</td>','')
                ws.write_merge(4, 4, 5, 6,dest.decode('utf-8'), style_title3)
                ws.write(4, 7, u'天数', style_title2)
                days=re.compile(r"线路天数:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if days:
                    days=days.replace('</td>','')
                ws.write_merge(4, 4, 8, 9, days.decode('utf-8'), style_title3)
                
                ws.write(5, 0, u'线路名称', style_title2)
                title=re.compile(r"线路名称:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if title:
                    title=title.replace('</td>','')
                ws.write_merge(5, 5, 1, 3, title.decode('utf-8'), style_title3)
                ws.write(5, 4, u'出团周期', style_title2)
                cycle=re.compile(r"出团周期:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if cycle:
                    cycle=cycle.replace('</td>','')
                ws.write_merge(5, 5, 5, 6, cycle.decode('utf-8'), style_title3)
                ws.write(5, 7, u'报名截止', style_title2)
                stopday=re.compile(r"报名截止:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if stopday:
                    stopday=stopday.replace('</td>','')
                ws.write_merge(5, 5, 8, 9, stopday.decode('utf-8'), style_title3)
                
                ws.write(6, 0, u'线路方向', style_title2)
                direction=re.compile(r"线路方向:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if direction:
                    direction=direction.replace('</td>','')
                ws.write_merge(6, 6, 1, 9, direction.decode('utf-8'), style_title3)
                ws.write(7, 0, u'线路特色', style_title2)
                speciality=re.compile(r"线路特色:[^>]*>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if speciality:
                    speciality=speciality.replace('</td>','')
                ws.write_merge(7, 7, 1, 9, speciality.decode('utf-8'), style_title3)
                
                ws.write_merge(9, 9, 0, 10, u'每日行程信息', style_title1)
                begin_i=10
                schedules=re_schedule.findall(html2)
                i=0
                for s in schedules:
                    begin_i=i*3+9+1
                    i=i+1
                    ws.write_merge(begin_i,begin_i+2,0,0, 'D'+str(i), style_title4)
                    ws.write(begin_i, 1, u'从', style_title4)
                    ws.write(begin_i, 2, s[0].decode('utf-8'), style_title3)
                    ws.write(begin_i, 3, u'到', style_title4)
                    ws.write(begin_i, 4, s[1].decode('utf-8'), style_title3)
                    ws.write(begin_i, 5, u'交通', style_title4)
                    ws.write(begin_i, 6, s[2].decode('utf-8'), style_title3)
                    ws.write(begin_i, 7, u'住宿', style_title4)
                    ws.write(begin_i, 8, s[3].decode('utf-8'), style_title3)
                    ws.write(begin_i, 9, u'用餐', style_title4)
                    ws.write(begin_i, 10, s[4].decode('utf-8'), style_title3)
                    ws.write_merge(begin_i+1,begin_i+2,1,10, s[5].replace('</td>','').decode('utf-8'), style_title3)
                    
                begin_i=begin_i+4
                ws.write_merge(begin_i, begin_i, 0, 10, u'其他信息', style_title1)
                ws.write_merge(begin_i+1,begin_i+9,0,0, u'服务标准', style_title4)
                ws.write(begin_i+1, 1, u'住宿', style_title4)
                hotel=re.compile(r">服务标准:</td>[\S\s]*?住宿:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if hotel:
                    hotel=hotel.replace('</td>','')
                ws.write_merge(begin_i+1,begin_i+1,2,10, hotel.decode('utf-8'), style_title3)
                ws.write(begin_i+2, 1, u'用餐', style_title4)
                meal=re.compile(r">服务标准:</td>[\S\s]*?用餐:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if meal:
                    hotel=meal.replace('</td>','')
                ws.write_merge(begin_i+2,begin_i+2,2,10, meal.decode('utf-8'), style_title3)
                ws.write(begin_i+3, 1, u'景点', style_title4)
                sights=re.compile(r">服务标准:</td>[\S\s]*?景点:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if sights:
                    sights=sights.replace('</td>','')
                ws.write_merge(begin_i+3,begin_i+3,2,10, sights.decode('utf-8'), style_title3)
                ws.write(begin_i+4, 1, u'保险', style_title4)
                insure=re.compile(r">服务标准:</td>[\S\s]*?保险:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if insure:
                    insure=insure.replace('</td>','')
                ws.write_merge(begin_i+4,begin_i+4,2,10, insure.decode('utf-8'), style_title3)
                ws.write(begin_i+5, 1, u'用车', style_title4)
                car=re.compile(r">服务标准:</td>[\S\s]*?用车:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if car:
                    car=car.replace('</td>','')
                ws.write_merge(begin_i+5,begin_i+5,2,10, car.decode('utf-8'), style_title3)
                ws.write(begin_i+6, 1, u'导服', style_title4)
                guide=re.compile(r">服务标准:</td>[\S\s]*?导服:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if guide:
                    guide=guide.replace('</td>','')
                ws.write_merge(begin_i+6,begin_i+6,2,10,guide.decode('utf-8'), style_title3)
                ws.write(begin_i+7, 1, u'交通', style_title4)
                traffic=re.compile(r">服务标准:</td>[\S\s]*?交通:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if traffic:
                    traffic=traffic.replace('</td>','')
                ws.write_merge(begin_i+7,begin_i+7,2,10, traffic.decode('utf-8'), style_title3)
                ws.write(begin_i+8, 1, u'购物', style_title4)
                shopping=re.compile(r">服务标准:</td>[\S\s]*?购物:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if shopping:
                    shopping=shopping.replace('</td>','')
                ws.write_merge(begin_i+8,begin_i+8,2,10, shopping.decode('utf-8'), style_title3)
                ws.write(begin_i+9, 1, u'其它', style_title4)
                other=re.compile(r">服务标准:</td>[\S\s]*?其它:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if other:
                    other=other.replace('</td>','')
                ws.write_merge(begin_i+9,begin_i+9,2,10,other.decode('utf-8'), style_title3)
                ws.write(begin_i+10, 0, u'自费项目', style_title4)
                payown=re.compile(r"自费项目:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if payown:
                    payown=payown.replace('</td>','')
                ws.write_merge(begin_i+10,begin_i+10,1,10,payown.decode('utf-8'), style_title3)
                ws.write(begin_i+11, 0, u'儿童标准', style_title4)
                children=re.compile(r"儿童标准:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if children:
                    children=children.replace('</td>','')
                ws.write_merge(begin_i+11,begin_i+11,1,10, children.decode('utf-8'), style_title3)
                ws.write(begin_i+12, 0, u'赠送项目', style_title4)
                present=re.compile(r"赠送项目:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if present:
                    present=present.replace('</td>','')
                ws.write_merge(begin_i+12,begin_i+12,1,10, present.decode('utf-8'), style_title3)
                ws.write(begin_i+13, 0, u'温馨提示', style_title4)
                prompt=re.compile(r"温馨提示:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if prompt:
                    prompt=prompt.replace('</td>','')
                ws.write_merge(begin_i+13,begin_i+13,1,10, prompt.decode('utf-8'), style_title3)
                ws.write(begin_i+14, 0, u'总社提示', style_title4)
                prompt2=re.compile(r"总社提示:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if prompt2:
                    prompt2=prompt2.replace('</td>','')
                ws.write_merge(begin_i+14,begin_i+14,1,10, prompt2.decode('utf-8'), style_title3)
                ws.write(begin_i+15, 0, u'参团须知', style_title4)
                mustknown=re.compile(r"参团须知:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if mustknown:
                    mustknown=mustknown.replace('</td>','')
                ws.write_merge(begin_i+15,begin_i+15,1,10, mustknown.decode('utf-8'), style_title3)
                ws.write(begin_i+16, 0, u'签证', style_title4)
                ws.write_merge(begin_i+16,begin_i+16,1,10, u'', style_title3)
                ws.write(begin_i+17, 0, u'地接信息', style_title4)
                localinfo=re.compile(r"地接信息:</td>[^>]*>\s*([\s\S]*?</td>)", re.S|re.X).search(html2).group(1)
                if localinfo:
                    localinfo=localinfo.replace('</td>','')
                ws.write_merge(begin_i+17,begin_i+17,1,10, localinfo.decode('utf-8'), style_title3)
                    
                begin_i=begin_i+19
                ws.write_merge(begin_i, begin_i, 0, 6, u'价格信息', style_title1)
                ws.write(begin_i+1, 0, u'发团时间', style_title1)
                ws.write_merge(begin_i+1, begin_i+1, 1, 2, u'成人价格', style_title1)
                ws.write_merge(begin_i+1, begin_i+1, 3, 4, u'儿童价格', style_title1)
                ws.write_merge(begin_i+1, begin_i+1, 5, 6, u'单房差', style_title1)
                x=begin_i+1
                prices=re_prices.findall(html2)
                i=0
                for p in prices:
                    begin_i=i*2+1+x
                    i=i+1
                    ws.write_merge(begin_i,begin_i+1,0,0, p[0], style_title3)
                    ws.write(begin_i,1, u'结算价', style_title3)
                    ws.write(begin_i+1,1, u'外卖价', style_title3)
                    ws.write(begin_i,2, p[1], style_title6)
                    ws.write(begin_i+1,2, p[4], style_title5)
                    ws.write(begin_i,3, u'结算价', style_title3)
                    ws.write(begin_i+1,3, u'外卖价', style_title3)
                    ws.write(begin_i,4, p[2], style_title6)
                    ws.write(begin_i+1,4, p[5], style_title5)
                    ws.write(begin_i,5, u'结算价', style_title3)
                    ws.write(begin_i+1,5, u'外卖价', style_title3)
                    ws.write(begin_i,6, p[3], style_title6)
                    ws.write(begin_i+1,6, p[6], style_title5)
            except Exception,e0:
                ws.write(20,1,u'出错了！艹，'+str(e0),style_title5)
                continue
            
            w.save('f:/routes/p_'+fname.decode('utf-8')+'.xls')
        next=re_nextPage.search(html)
        if next:
            writeXls(next.group(2))
    except Exception,e1:
        print get_url
        print e1
    #print w.__worksheets
    #w.__worksheets[0]=sheets[0]
    print 'over'
    
if __name__ == '__main__':
    #downImage('http://byts.cn/work/inc/image.jsp')
    #do_crawl(url % ('1'))
    writeXls(url % ('1'))
    
